#include <QtTest>
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QMutex>
#include <QSqlQuery>
#include <QSqlError>
#include <QSqlRecord>
#include <QSqlField>

// add necessary includes here

class Sqlite : public QObject
{
    Q_OBJECT

public:
    Sqlite();
    ~Sqlite();

private slots:
    void initTestCase();
    void cleanupTestCase();
    void test_add_rows();
    void test_add_rows2();
    void test_query_row();
    void test_row_count();
    void test_table_header();
private:
    QSqlDatabase *m_pDatabase;
};

Sqlite::Sqlite()
{

}

Sqlite::~Sqlite()
{

}

void Sqlite::initTestCase()
{
    QString dbFileName = QCoreApplication::applicationDirPath() + "/test.db";

    qDebug()<< "db:" << dbFileName;

    m_pDatabase = new QSqlDatabase();
    *m_pDatabase = QSqlDatabase::addDatabase("QSQLITE","tst_sqlite");
    m_pDatabase->setDatabaseName(dbFileName);
    if(!m_pDatabase->open()){
        qWarning()<< "db open failed:" << dbFileName;
    }

    //创建表
    QString strCreateTable =  "CREATE TABLE TableInfo (\
                                    id          INTEGER       PRIMARY KEY AUTOINCREMENT \
                                                              NOT NULL, \
                                    section     VARCHAR (150), \
                                    curGroupID  VARCHAR (150), \
                                    storageType INTEGER \
                                );" ;


    QSqlQuery query = m_pDatabase->exec(strCreateTable);
    if(query.isActive()){
        qDebug()<<"表创建成功 所有表:"<< m_pDatabase->tables();
    }

}

void Sqlite::cleanupTestCase()
{
//    QSqlQuery query = m_pDatabase->exec("drop table TableInfo;");
//    if(query.isActive()){
//        qDebug()<<"表删除成功:"<< m_pDatabase->tables();
//    }
}

void Sqlite::test_add_rows()
{
    QString sql = "INSERT INTO TableInfo values(?,?,?,?)";
    for (int i =0 ; i < 20; i++) {
        QSqlQuery query(*m_pDatabase);
        query.prepare(sql);
        query.addBindValue(i);
        query.addBindValue("settings");
        query.addBindValue(QString("groupId:%1").arg(i));
        query.addBindValue(i);

        query.exec();
        if(query.isActive())
            qDebug()<<"添加数据语句执行成功";
        else
            qDebug()<<"执行失败:" << query.lastError();

    }
}

void Sqlite::test_add_rows2()
{
    QString sql = "INSERT INTO TableInfo values(?,?,?,?)";
    m_pDatabase->transaction(); //开启事务
    for (int i =20 ; i < 40; i++) {
        QSqlQuery query(*m_pDatabase);
        query.prepare(sql);
        query.addBindValue(i);
        query.addBindValue("settings");
        query.addBindValue(QString("groupId:%1").arg(i));
        query.addBindValue(i);

        query.exec();
        if(query.isActive())
            qDebug()<<"添加数据语句执行成功";
        else
            qDebug()<<"执行失败:" << query.lastError();

    }
    m_pDatabase->commit();
}

void Sqlite::test_query_row()
{
    QString sql = "select * from TableInfo where id = '1'";

    QSqlQuery query(*m_pDatabase);
    query.prepare(sql);
    query.exec();
    if(query.isActive())
        qDebug()<<"查询数据语句执行成功";
    else
        qDebug()<<"查询数据执行失败:" << query.lastError();

    QSqlRecord record = query.record(); //返回查询结果的表头  包含结果有多少列  和列名
    for (int i = 0; i < record.count(); i++) {
        QSqlField field = record.field(i);
        qDebug() <<"列："<< i << field.name() << field.type() << field.tableName() << field.value();
    }
    query.first();
//    while (query.next()) {
        for(int i =0; i < record.count(); i++){
            qDebug() <<"val:" << query.value(i);
        }
        //    }
}

void Sqlite::test_row_count()
{
    //查询表总行数
    int rowCnt = 0;
    QString sql = "select Count(*) from TableInfo;";

    QSqlQuery query(*m_pDatabase);
    query.prepare(sql);
    query.exec();
    if(query.isActive())
        qDebug()<<"查询行数语句执行成功";
    else
        qDebug()<<"查询数据执行失败:" << query.lastError();



    if(query.isActive()){
        if(query.first()){
            rowCnt = query.value(0).toInt();
        }
    }

    qDebug()<< "数据表行数:" << rowCnt;
}

void Sqlite::test_table_header()
{
    //    QString sql = "PRAGMA table_info(TableInfo);";

}



QTEST_MAIN(Sqlite)

#include "tst_sqlite.moc"
